﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;
using System.Text;
using Dapper;

namespace AyuBookmark.Common.Extensions
{
    /// <summary>
    /// SQL相关扩展方法
    /// </summary>
    public static class SqlExtensions
    {
        /// <summary>
        /// 将条件表达式转换为WHERE子句
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="predicate">条件表达式</param>
        /// <param name="parameters">输出参数</param>
        /// <param name="parameterPrefix">参数前缀</param>
        /// <returns>WHERE子句</returns>
        public static string ToSqlWhereClause<T>(
            this Expression<Func<T, bool>> predicate,
            out DynamicParameters parameters,
            string parameterPrefix = "p")
        {
            parameters = new DynamicParameters();
            if (predicate == null) return string.Empty;

            var visitor = new SqlExpressionVisitor(parameterPrefix);
            visitor.Visit(predicate);

            parameters = visitor.Parameters;
            return visitor.GetWhereClause();
        }

        /// <summary>
        /// 将排序表达式转换为ORDER BY子句
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <typeparam name="TKey">排序键类型</typeparam>
        /// <param name="orderBy">排序表达式</param>
        /// <param name="isDescending">是否降序</param>
        /// <returns>ORDER BY子句</returns>
        public static string ToSqlOrderByClause<T, TKey>(
            this Expression<Func<T, TKey>> orderBy,
            bool isDescending = true)
        {
            if (orderBy == null) return string.Empty;

            var memberExpression = orderBy.Body as MemberExpression;
            if (memberExpression == null)
            {
                throw new ArgumentException("OrderBy expression must be a member access expression");
            }

            return $"{memberExpression.Member.Name} {(isDescending ? "DESC" : "ASC")}";
        }

        /// <summary>
        /// 构建分页SQL语句
        /// </summary>
        /// <param name="sql">原始SQL</param>
        /// <param name="pageIndex">页码(从1开始)</param>
        /// <param name="pageSize">每页记录数</param>
        /// <param name="orderBy">排序字段</param>
        /// <returns>分页SQL</returns>
        public static string BuildPagedQuery(
            this string sql,
            int pageIndex,
            int pageSize,
            string orderBy = "Id")
        {
            if (pageIndex < 1) throw new ArgumentOutOfRangeException(nameof(pageIndex));
            if (pageSize < 1) throw new ArgumentOutOfRangeException(nameof(pageSize));

            return $@"
                {sql}
                ORDER BY {orderBy}
                LIMIT {pageSize} OFFSET {(pageIndex - 1) * pageSize}";
        }

        /// <summary>
        /// 合并多个DynamicParameters
        /// </summary>
        public static DynamicParameters Merge(
            this DynamicParameters target,
            params DynamicParameters[] sources)
        {
            foreach (var source in sources.Where(s => s != null))
            {
                foreach (var name in source.ParameterNames)
                {
                    target.Add(name, source.Get<object>(name));
                }
            }
            return target;
        }

        /// <summary>
        /// 将对象属性转换为DynamicParameters
        /// </summary>
        public static DynamicParameters ToDynamicParameters(
            this object obj,
            string prefix = null,
            IEnumerable<string> excludeProperties = null)
        {
            var parameters = new DynamicParameters();
            if (obj == null) return parameters;

            var properties = obj.GetType().GetProperties();
            foreach (var property in properties)
            {
                if (excludeProperties?.Contains(property.Name) == true)
                    continue;

                var paramName = string.IsNullOrEmpty(prefix)
                    ? property.Name
                    : $"{prefix}{property.Name}";

                parameters.Add(paramName, property.GetValue(obj));
            }

            return parameters;
        }

        /// <summary>
        /// SQL表达式访问器（内部类）
        /// </summary>
        private class SqlExpressionVisitor : ExpressionVisitor
        {
            private readonly StringBuilder _whereBuilder = new StringBuilder();
            private readonly string _paramPrefix;
            private int _paramCounter;
            public DynamicParameters Parameters { get; } = new DynamicParameters();

            public SqlExpressionVisitor(string paramPrefix = "p")
            {
                _paramPrefix = paramPrefix;
            }

            public string GetWhereClause() => _whereBuilder.ToString();

            protected override Expression VisitBinary(BinaryExpression node)
            {
                _whereBuilder.Append("(");
                Visit(node.Left);

                switch (node.NodeType)
                {
                    case ExpressionType.Equal:
                        _whereBuilder.Append(" = ");
                        break;
                    case ExpressionType.NotEqual:
                        _whereBuilder.Append(" <> ");
                        break;
                    case ExpressionType.GreaterThan:
                        _whereBuilder.Append(" > ");
                        break;
                    case ExpressionType.GreaterThanOrEqual:
                        _whereBuilder.Append(" >= ");
                        break;
                    case ExpressionType.LessThan:
                        _whereBuilder.Append(" < ");
                        break;
                    case ExpressionType.LessThanOrEqual:
                        _whereBuilder.Append(" <= ");
                        break;
                    case ExpressionType.AndAlso:
                        _whereBuilder.Append(" AND ");
                        break;
                    case ExpressionType.OrElse:
                        _whereBuilder.Append(" OR ");
                        break;
                    default:
                        throw new NotSupportedException($"运算符 {node.NodeType} 不支持");
                }

                Visit(node.Right);
                _whereBuilder.Append(")");
                return node;
            }

            protected override Expression VisitMember(MemberExpression node)
            {
                _whereBuilder.Append(node.Member.Name);
                return node;
            }

            protected override Expression VisitConstant(ConstantExpression node)
            {
                var paramName = $"{_paramPrefix}{++_paramCounter}";
                Parameters.Add(paramName, node.Value);
                _whereBuilder.Append($"@{paramName}");
                return node;
            }

            protected override Expression VisitMethodCall(MethodCallExpression node)
            {
                if (node.Method.Name == "Contains")
                {
                    Visit(node.Object);
                    _whereBuilder.Append(" LIKE ");
                    Visit(node.Arguments[0]);
                    _whereBuilder.Append(" + '%'");
                    return node;
                }

                throw new NotSupportedException($"方法 {node.Method.Name} 不支持");
            }
        }
    }
}